﻿//#undef MySql
//#define MSSql
#define MySql

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

using MySql.Data.MySqlClient;

namespace ukeasbjWeb
{
    public partial class Expense : System.Web.UI.Page
    {
        string strCnn = ConfigurationManager.AppSettings["glCnnStr"]; 

        protected void Page_Load(object sender, EventArgs e)
        {
            // 在此处放置用户代码以初始化页面
            Common_SXD.Is_LongIn(Response, Session, Request);

            bool bIs_Cashier = Convert.ToBoolean(Session["Is_Cashier"]);
            bool bIs_Admin = Convert.ToBoolean(Session["Is_Admin"]);
            bool bIs_Accountant = Convert.ToBoolean(Session["Is_Accountant"]);
            bool bIs_Collector = Convert.ToBoolean(Session["Is_Collector"]);

            if (!bIs_Cashier && !bIs_Admin && !bIs_Accountant && !bIs_Collector)
            {
                Response.Write("<h2 align='center'><font face='宋体' color='red'>");
                Response.Write("<br>对不起，您不能使用本功能。" +
                    "<br>请用其它身份登录。");
                Response.Write("</fong></h2>");
                Response.End();

            }

            if (!IsPostBack)
            {	// 初始化页面
                /*
                SqlConnection sqlCnn = new SqlConnection(strCnn);
                // 载入合同类别数据，绑定到 DDList_ContractClass 中
                string sSql = "select * from Student_Type order by Code_Student_Type asc";
                SqlDataAdapter sqlDA = new SqlDataAdapter(sSql, sqlCnn);
                sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                DataSet bsDataSet = new DataSet();
                //				sqlDA.Fill(bsDataSet, "Student_Type");
                //				DDList_ContractClass.DataSource = bsDataSet.Tables["Student_Type"];
                //				DDList_ContractClass.DataBind();
                sqlCnn.Close();
                */
                //			Init_Column_Translation();
                Init_Time_Control();
                Init_Table_Expense();
                Init_Question_Expense();
                //			Grid_BindData();


                // 载入银行类别数据，绑定到 DDList_BankClass 中
                string sSql = "select * from Cash_Counters where Cash_Counters not like '现金' order by Code_Cash_Counters asc";
                Common_SXD.Init_DropDownList(DDList_BankClass, "Cash_Counters", sSql);
                
                /*
                sqlDA = new SqlDataAdapter(sSql, sqlCnn);
                sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                bsDataSet = new DataSet();
                sqlDA.Fill(bsDataSet, "Cash_Counters");
                DDList_BankClass.DataSource = bsDataSet.Tables["Cash_Counters"];
                DDList_BankClass.DataBind();
                sqlCnn.Close();
                */

            }
            if (!IsPostBack)
            {	// 初始化页面
                /*
                SqlConnection sqlCnn = new SqlConnection(strCnn);
                // 载入地区类别数据，绑定到 DDList_OfficeClass 中
                string sSql = "select * from Office order by Code_Office asc";
                SqlDataAdapter sqlDA = new SqlDataAdapter(sSql, sqlCnn);
                sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                DataSet bsDataSet = new DataSet();
                sqlDA.Fill(bsDataSet, "Office");
                DDList_OfficeClass.DataSource = bsDataSet.Tables["Office"];
                DDList_OfficeClass.DataBind();
                sqlCnn.Close();
                */
                Common_SXD.Init_Office(DDList_OfficeClass);
                Common_SXD.SetSelectedIndex(Session["Office"].ToString().Trim(), DDList_OfficeClass);
            }

        }

        private void Init_Time_Control()
        {
            ////////日期控件初始化
            int tMonth = DateTime.Now.Month;
            int tYear = DateTime.Now.Year;

            tMonth -= 4;  // 起点时间为：4个月前
            if (tMonth < 1)
            {
                tMonth += 12;
                tYear -= 1;
            }

            System.DateTime startDateTime =
                new System.DateTime(tYear,        // Year
                tMonth,            // Month
                1,            // Day
                0,            // Hour
                0,            // Minute
                0,            // Second
                0 // ,            // Millisecond
                //			calendar    // Calendar
                );

            TextBox_Date_start_occur.Text = Common_SXD.TimeToYMD(startDateTime.ToString());
            TextBox_Date_end_occur.Text = Common_SXD.TimeToYMD(DateTime.Now.ToString());


        }
        /// <summary>
        /// 问题支出查询，初始化
        /// </summary>
        private void Init_Question_Expense()
        {

            bool bIs_Cashier = Convert.ToBoolean(Session["Is_Cashier"]);
            bool bIs_Admin = Convert.ToBoolean(Session["Is_Admin"]);
            bool bIs_Accountant = Convert.ToBoolean(Session["Is_Accountant"]);
            //			bool bIs_Collector = Convert.ToBoolean(Session["Is_Collector"]);

            if (bIs_Cashier || bIs_Admin || bIs_Accountant)
            {
                DDList_OfficeClass.Enabled = true;

            }
            else
                DDList_OfficeClass.Enabled = false;

        }


        /// <summary>
        /// 初始化支出表
        /// </summary>
        private void Init_Table_Expense()
        {
            //  在此处放置用户代码以初始化页面
            //////////////////////////////////////////////////////
            string sOClassID = DDList_OfficeClass.SelectedValue; //   SelectedItem.Text;
            string sBankClassID = DDList_BankClass.SelectedValue;
            string sSearchValue = TextBox_SearchValue.Text.Trim();

            // 根据输入条件，生成 sql 查询语句
            /*
            SqlConnection sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            SqlCommand sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象
            */
            string sSql = "select WasteBook_No, Brief, Date_Occur, Credit_Income, Debit_Payout, Notes, Cash_Counters, Series_No from WasteBook  where ";
            //   支出大于零
            sSql += " Cash_Counters = '" + sBankClassID + "' and ";
            sSql += "( Brief like '%?%' or  Brief like '%？%') and ";

            if (sOClassID != "总部")
                sSql += " Brief like '%" + sOClassID + "%' and ";

            sSql += "( Date_Occur  >= '" + TextBox_Date_start_occur.Text + "' and  Date_Occur  <= '" + TextBox_Date_end_occur.Text + "') and ";
            sSql += "Credit_Income <= 0 and Debit_Payout >0 and (Brief like '%" + sSearchValue + "%' or Notes like '%" + sSearchValue + "%' or WasteBook_No like '%" + sSearchValue + "%' )";
            sSql += " order by WasteBook_No ASC ";

            ///sqlCmd.CommandText = sSql;
#if MSSql
            SqlConnection sqlCnn = new SqlConnection();
            SqlDataReader sqlReader = Common_SXD.Open_MSSqlDataReader(sSql, sqlCnn, "打开数据库出错", Response);
            
            int nCol = sqlReader.FieldCount;

            //清除表中的内容
            Table_Expense.Rows.Clear();
            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }
                Table_Expense.Rows.Add(row);
                row.BgColor = "Tan";
            }
            // 依次读出数据
            int j = 0;
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);
                // Provide a different background color for alternating rows.
                if (j % 2 == 1)
                    row.BgColor = "Gainsboro";

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                }

                Table_Expense.Rows.Add(row);
                j++;
            }

            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接
#elif MySql
            MySqlConnection MySqlconn = new MySqlConnection();
            MySqlDataReader MysqlReader = Common_SXD.Open_MySqlDataReader(sSql, MySqlconn, "打开数据库出错", Response);

            int nCol = MysqlReader.FieldCount;

            //清除表中的内容
            Table_Expense.Rows.Clear();
            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    string temp = Common_SXD.English_Chinese(MysqlReader.GetName(i));
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }
                Table_Expense.Rows.Add(row);
                row.BgColor = "Tan";
            }
            // 依次读出数据
            int j = 0;
            while (MysqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = MysqlReader.GetString(0);
                // Provide a different background color for alternating rows.
                if (j % 2 == 1)
                    row.BgColor = "Gainsboro";

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(MysqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                }

                Table_Expense.Rows.Add(row);
                j++;
            }

            MysqlReader.Close(); // 关闭sqDataReader
            MySqlconn.Close(); // 关闭数据库连接

#endif
        }

        protected void Bt_Search_Click(object sender, EventArgs e)
        {
            // 点击查询按钮后，重新绑定数据
            Init_Table_Expense();
        }

    }
}
